package com.parse.util;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelUtil {
	// httpclient htmlparser
	public static void changeListToExcel(String filePath, List<List<String>> list) throws IOException {
		System.out.println("创建Excel。。。");
		// 创建HSSFWorkbook对象
		HSSFWorkbook wb = new HSSFWorkbook();
		// 创建HSSFSheet对象
		HSSFSheet sheet = wb.createSheet("sheet1");
		// 创建HSSFRow对象
		for (int i = 0; i < list.size(); i++) {
			HSSFRow row = sheet.createRow(i);
			List<String> rowValue = list.get(i);
			for (int j = 0; j < rowValue.size(); j++) {
				// 创建HSSFCell对象
				HSSFCell cell = row.createCell(j);
				// 设置单元格的值
				cell.setCellValue(rowValue.get(j));
			}
		}

		// 输出Excel文件
		FileOutputStream output = new FileOutputStream(filePath);
		wb.write(output);
		output.flush();
		wb.close();
	}

	/**
	 * change excel to a list. { @param start } start with 1
	 * 
	 * @param filePath
	 * @param start
	 * @return
	 */
	public static List<List<Object>> changeExcelToList(String filePath, int start) {
		List<List<Object>> list = new ArrayList<>();
		Workbook wb = null;
		Sheet sheet = null;
		Row row = null;
		Cell cell = null;
		boolean isXlsx = filePath.endsWith("xlsx");
		try {
			if (isXlsx) {
				wb = new XSSFWorkbook(new FileInputStream(filePath));
			} else {
				wb = new HSSFWorkbook(new FileInputStream(filePath));
			}
			sheet = wb.getSheetAt(0);
			int cellNum = Integer.MAX_VALUE;

			for (int i = 0; i < sheet.getLastRowNum(); i++) {
				List<Object> temp = new ArrayList<>();
				row = sheet.getRow(i);
				if (i == 0) {
					cellNum = row.getLastCellNum();
				}
				for (int j = 0; j < row.getLastCellNum() || j < cellNum; j++) {
					cell = row.getCell(j);
					Object result = getCellValue(cell, isXlsx);
					temp.add(result);
				}
				if (i >= start - 1) {
					list.add(temp);
				}

			}

		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				wb.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}

		return list;
	}

	private static Object getCellValue(Cell cell, boolean isXlsx) {
		if (cell == null) {
			return "";
		}
		Object result = null;
		if (isXlsx) {
			switch (cell.getCellType()) {
			case XSSFCell.CELL_TYPE_STRING:
				result = cell.getStringCellValue();
				break;
			case XSSFCell.CELL_TYPE_NUMERIC:
				DecimalFormat df = new DecimalFormat("0");
				result = df.format(cell.getNumericCellValue());
				break;
			case XSSFCell.CELL_TYPE_FORMULA:
				result = cell.getCellFormula();
				break;
			case XSSFCell.CELL_TYPE_ERROR:
				result = cell.getErrorCellValue();
				break;
			case XSSFCell.CELL_TYPE_BOOLEAN:
				result = cell.getBooleanCellValue();
				break;
			case XSSFCell.CELL_TYPE_BLANK:
				result = "";
				break;
			default:
				result = "";
				break;
			}
		} else {
			switch (cell.getCellType()) {
			case HSSFCell.CELL_TYPE_STRING:
				result = cell.getStringCellValue();
				break;
			case HSSFCell.CELL_TYPE_NUMERIC:
				result = cell.getNumericCellValue();
				break;
			case HSSFCell.CELL_TYPE_FORMULA:
				result = cell.getCellFormula();
				break;
			case HSSFCell.CELL_TYPE_ERROR:
				result = cell.getErrorCellValue();
				break;
			case HSSFCell.CELL_TYPE_BOOLEAN:
				result = cell.getBooleanCellValue();
				break;
			case HSSFCell.CELL_TYPE_BLANK:
				result = "";
				break;
			default:
				result = "";
				break;
			}
		}

		return result;
	}

}
